﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Data.Linq;
using Settings;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;

namespace Sami_SMS_CRM_Business
{
    public class SubscriberImportRepository : Repository
    {
        public DataTable GetAll(string subscriber, int? collection_id, int? service_id, int currentPage, int pageSize, ref int totalRecord)
        {
            DataTable retTable = new DataTable();
            StringBuilder whereClause = new StringBuilder("WHERE 1=1");
            if (!string.IsNullOrEmpty(subscriber))
                whereClause.AppendLine("AND ci.subs LIKE @subscriber");
            if (collection_id != null)
                whereClause.AppendLine("AND @collection_id = ci.collection_id");
            switch (service_id)
            {
                case null:
                    whereClause.AppendLine("AND ci.service_id IS NULL");
                    break;
                case -1:

                    break;
                default:
                    whereClause.Append("AND @service_id = ci.service_id");
                    break;
            }
            string query = string.Format(@"
                DECLARE @tmp TABLE (
                    [No] INT,
                    guid_key UNIQUEIDENTIFIER PRIMARY KEY
                )
                INSERT INTO @tmp
                SELECT ROW_NUMBER() OVER (ORDER BY vasp_num), ci.guid_key
                FROM crm_subscriber_import ci
                {0}
                SELECT ci.*,
                (SELECT collection_name FROM crm_collection WHERE collection_id = ci.collection_id) collection,
                (SELECT service_name FROM crm_service WHERE sevice_id = ci.service_id) service
                FROM crm_subscriber_import ci INNER JOIN @tmp T ON ci.guid_key = T.guid_key 
                WHERE T.[No] BETWEEN {1} AND {2}
                ORDER BY vasp_num
                SELECT @retValue = COUNT(*) FROM @tmp",
                whereClause, (currentPage - 1) * pageSize + 1, currentPage * pageSize);
            using (SqlConnection conn = new SqlConnection(Settings.LocalSettings.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@subscriber", "%" + subscriber + "%" ?? (object)DBNull.Value);
                    cmd.Parameters.AddWithValue("@collection_id", collection_id ?? (object)DBNull.Value);
                    cmd.Parameters.AddWithValue("@service_id", service_id ?? (object)DBNull.Value);
                    cmd.Parameters.Add("@retValue", SqlDbType.Int).Direction = ParameterDirection.Output;
                    retTable.Load(cmd.ExecuteReader());
                    totalRecord = (int)cmd.Parameters["@retValue"].Value;
                }
            }
            return retTable;
        }

        public crm_subscriber_import GetByID(object id)
        {
            return DB.crm_subscriber_imports.FirstOrDefault(p => p.guid_key.Equals(id));
        }

        public void Insert(crm_subscriber_import entitty)
        {
            DB.crm_subscriber_imports.InsertOnSubmit(entitty);
            DB.SubmitChanges();
        }

        public void SaveChange()
        {
            DB.SubmitChanges();
        }

        public void DeleteByID(object Id)
        {
            using (SqlConnection conn = new SqlConnection(Settings.LocalSettings.ConnectionString))
            {
                conn.Open();
                string query = string.Format("DELETE FROM crm_subscriber_import WHERE guid_key='{0}'", Id); ;
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static void BulkInsert(string filePath, string collection_id, string service_id)
        {
            string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", filePath);
            using (OleDbConnection Oleconn = new OleDbConnection(excelConnectionString))
            {
                Oleconn.Open();
                using (OleDbCommand Olecmd = Oleconn.CreateCommand())
                {
                    Olecmd.CommandText = string.Format(@"SELECT subscriber, {0} as collection_id, vasp_num, operator_id, gateway_id, {1} as service_id FROM [Sheet1$]",
                                                collection_id, string.IsNullOrEmpty(service_id) ? "NULL" : service_id);
                    using (SqlConnection conn = new SqlConnection(LocalSettings.ConnectionString))
                    {
                        conn.Open();
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            string tempTableName = "dbo.Temp" + Guid.NewGuid().ToString("N"); 
                            try
                            {
                                //Tạo 1 bảng tạm chưa dữ liệu
                                cmd.CommandText = string.Format(@"SELECT TOP(0) subscriber,collection_id,vasp_num,operator_id,
                                                    gateway_id,service_id INTO {0} FROM crm_subscriber_import", tempTableName);
                                cmd.ExecuteNonQuery();
                                //Insert dữ liệu vào bảng tạm
                                using (SqlBulkCopy bc = new SqlBulkCopy(conn))
                                {
                                    bc.BulkCopyTimeout = 0;
                                    bc.DestinationTableName = tempTableName;
                                    bc.WriteToServer(Olecmd.ExecuteReader());
                                }
                                //Đẩy dữ liệu vào bảng gốc và xóa bảng tạm
                                cmd.CommandText =
                                    string.Format(@"
                                    INSERT INTO crm_subscriber_import(guid_key,subscriber,collection_id,vasp_num,operator_id,gateway_id,service_id)
                                    SELECT NEWID(),* 
                                    FROM (
	                                    SELECT DISTINCT * FROM {0} T
	                                    WHERE T.subscriber NOT IN ( 
		                                    SELECT subscriber FROM crm_subscriber_import csi 
		                                    WHERE csi.subscriber = T.subscriber 
			                                    AND csi.collection_id = T.collection_id
                                                AND ISNULL(csi.service_id,-1) = ISNULL(T.service_id,-1)
	                                    )
                                    )T
                                    DROP TABLE {0}", tempTableName);
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                cmd.CommandText = string.Format("DROP TABLE {0}", tempTableName);
                                cmd.ExecuteNonQuery();
                                throw e;
                            }
                           
                        }
                    }
                }
            }
        }
    }
}
